package com.yhty.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.yhty.dao.OweDao;
import com.yhty.model.Owe;
import com.yhty.model.OweList;
import com.yhty.model.Pay;
import com.yhty.utils.DateUtil;

/**
 * 欠费记录dao实现类
 * @author RoySong
 * 2011-3-30-下午02:55:10
 */
@Repository("oweDao")
public class OweDaoImpl implements OweDao {

	private JdbcTemplate jdbcTemplate;
	
	/* 新增欠费记录
	 * @see com.yhty.dao.OweDao#add(com.yhty.model.Owe)
	 */
	public void add(Owe owe) {
		String sql = "insert into owe values(OWE_SEQ_ID.NEXTVAL,"+
		owe.getRent_id()+","+owe.getOwe().getRent()+","+
		owe.getOwe().getManager()+","+owe.getOwe().getWater()+
		","+owe.getOwe().getElectric()+","+owe.getDelay()+","+
		owe.getOwe_last()+","
		+owe.getAll()+",'"+owe.getCreate_date()+"')";
		jdbcTemplate.update(sql);
	}

	@Resource(name="jdbcTemplate")
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	/*
	 * 根据日期获取对应月份的欠费记录
	 * @see com.yhty.dao.OweDao#getByDate(java.lang.String)
	 */
	public List<Owe> getByMonth(String dateStr) {
		int[] date = DateUtil.getYearMonthDay(dateStr);
		String sql = "select * from owe where year(create_date)="+
		date[0]+" and month(create_date)="+date[1];
		return jdbcTemplate.query(sql, new RowMapper<Owe>(){

			public Owe mapRow(ResultSet rs, int rowNo) throws SQLException {
				Owe owe = new Owe();
				Pay pay = new Pay();
				owe.setAll(rs.getFloat("all"));
				owe.setCreate_date(DateUtil.getDate(rs.getTimestamp("create_date")));
				owe.setDelay(rs.getFloat("delay"));
				owe.setId(rs.getLong("id"));
				owe.setRent_id(rs.getLong("rent_id"));
				pay.setRent(rs.getFloat("rent"));
				pay.setManager(rs.getFloat("manager"));
				pay.setWater(rs.getFloat("water"));
				pay.setElectric(rs.getFloat("electric"));
				owe.setOwe(pay);
				owe.setOwe_last(rs.getFloat("owe_last"));
				return owe;
			}
			
		});
	}

	/*
	 * 判断本月欠费记录是否已经自动生成
	 * @see com.yhty.dao.OweDao#isOweInit()
	 */
	public boolean isOweInit() {
		int[] today = DateUtil.getYearMonthDay(null);
		String sql = "select count(*) from init_status where "+
		" year(owe_date)="+today[0]+" and month(owe_date)="+
		today[1];
		int i = jdbcTemplate.queryForInt(sql);
		if(i == 0)
			return false;
		return true;
	}

	/*
	 * 取出本月应缴的房租物管费用
	 * @see com.yhty.dao.OweDao#getThisMonth()
	 */
	public List<Owe> getThisMonth() {
		String sql = "select id as rent_id,rental as rent," +
				"managmentExpenses as manager from rent where status=1 ";
		return jdbcTemplate.query(sql, new RowMapper<Owe>(){

			public Owe mapRow(ResultSet rs, int rowNum) throws SQLException {
				Owe o = new Owe();
				Pay owe = new Pay();
				owe.setRent(rs.getFloat("rent"));
				owe.setManager(rs.getFloat("manager"));
				o.setOwe(owe);
				o.setRent_id(rs.getLong("rent_id"));
				return o;
			}
			
		});
	}

	/*
	 * 批量插入本月欠费信息
	 * @see com.yhty.dao.OweDao#batchAdd(java.util.List)
	 */
	public void batchAdd(final List<Owe> thisMonth) {
		String sql = "insert into owe values(OWE_SEQ_ID.NEXTVAL," +
				"?,?,?,?,?,?,?,?,?)";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter(){

			public int getBatchSize() {
				return thisMonth.size();
			}

			public void setValues(PreparedStatement ps, int i)
					throws SQLException {
				ps.setLong(1, thisMonth.get(i).getRent_id());
				ps.setFloat(2, thisMonth.get(i).getOwe().getRent());
				ps.setFloat(3, thisMonth.get(i).getOwe().getManager());
				ps.setFloat(4, thisMonth.get(i).getOwe().getWater());
				ps.setFloat(5, thisMonth.get(i).getOwe().getElectric());
				ps.setFloat(6, thisMonth.get(i).getDelay());
				ps.setFloat(7, thisMonth.get(i).getOwe_last());
				ps.setFloat(8, thisMonth.get(i).getAll());
				ps.setString(9, DateUtil.getToday()); 
			}
			
		});
	}

	/*
	 * 更改初始化标识
	 * @see com.yhty.dao.OweDao#init()
	 */
	public void init() {
		String sql = "update init_status set owe_date='"
			+DateUtil.getToday()+"'";
		jdbcTemplate.update(sql);
	}

	/*
	 * 根据出租关系ID获取当月欠费记录
	 * @see com.yhty.dao.OweDao#getByRentId(java.lang.String)
	 */
	public Owe getByRentId(String today, Long rent_id) throws EmptyResultDataAccessException{
		int[] date = DateUtil.getYearMonthDay(today);
		String sql = "select * from owe where rent_id="+
		rent_id+" and year(create_date)="+date[0]+
		" and month(create_date)="+date[1];
		Owe o = null;
		try{
		o = jdbcTemplate.queryForObject(sql, new RowMapper<Owe>(){

			public Owe mapRow(ResultSet rs, int rowNum) throws SQLException {
				Owe owe = new Owe();
				owe.setAll(rs.getFloat("all"));
				owe.setCreate_date(DateUtil.getDate(rs.getTimestamp("create_date")));
				owe.setDelay(rs.getFloat("delay"));
				owe.setId(rs.getLong("id"));
				owe.setOwe_last(rs.getFloat("owe_last"));
				owe.setRent_id(rs.getLong("rent_id"));
				Pay o = new Pay();
				o.setElectric(rs.getFloat("electric"));
				o.setManager(rs.getFloat("manager"));
				o.setRent(rs.getFloat("rent"));
				o.setWater(rs.getFloat("water"));
				owe.setOwe(o);
				return owe;
			}
			
		});
		}catch(EmptyResultDataAccessException e){
			e.printStackTrace();
			return null;
		}
		return o;
	}

	/*
	 * 更新欠费记录
	 * @see com.yhty.dao.OweDao#update(com.yhty.model.Owe)
	 */
	public void update(Owe owe) {
		String sql = "update owe set rent="+owe.getOwe().getRent()
		+",manager="+owe.getOwe().getManager()+",water="
		+owe.getOwe().getWater()+",electric="+
		owe.getOwe().getElectric()+",delay="+
		owe.getDelay()+",owe_last="+owe.getOwe_last()+
		",all="+owe.getAll()+",create_date='"+
		DateUtil.getToday()+"' where id="+owe.getId();
		jdbcTemplate.update(sql);
	}

	/*
	 * 根据月份获取欠费记录(包含房屋和租房人信息)
	 * @see com.yhty.dao.OweDao#getOweListByMonth(java.lang.String)
	 */
	public List<OweList> getOweListByMonth(String date) {
		int[] now = DateUtil.getYearMonthDay(date);
		String sql = "SELECT h.file_code,h.hos_type,h.hos_floor,"+
		"h.location,h.bump_code,h.room_code,h.use_area,h.build_area,h.common_area,"+
		"r.customer,o.id as owe_id,o.rent_id,o.rent,o.manager,o.water,o.electric,"+
		"o.delay,o.owe_last,o.all,o.create_date FROM house h,owe o,rent r "+
		"WHERE h.file_code=r.file_code and r.status='1' and r.id=o.rent_id "+
		"and YEAR(o.create_date)="+now[0]+" and MONTH(o.create_date)="+
		now[1]+" and convert(o.all,float)>0 order by o.rent_id";
		return jdbcTemplate.query(sql, new RowMapper<OweList>(){

			public OweList mapRow(ResultSet rs, int rowNum) throws SQLException {
				OweList ol = new OweList();
				ol.setAll(rs.getFloat("all"));
				ol.setBuild_area(rs.getString("build_area"));
				ol.setBump_code(rs.getString("bump_code"));
				ol.setCommon_area(rs.getString("common_area"));
				ol.setCreate_date(DateUtil.getDate(rs.getTimestamp("create_date")));
				ol.setCustomer(rs.getString("customer"));
				ol.setDelay(rs.getFloat("delay"));
				ol.setElectric(rs.getFloat("electric"));
				ol.setFile_code(rs.getString("file_code"));
				ol.setHos_floor(rs.getString("hos_floor"));
				ol.setHos_type(rs.getString("hos_type"));
				ol.setLocation(rs.getString("location"));
				ol.setManager(rs.getFloat("manager"));
				ol.setOwe_id(rs.getLong("owe_id"));
				ol.setOwe_last(rs.getFloat("owe_last"));
				ol.setRent(rs.getFloat("rent"));
				ol.setRent_id(rs.getLong("rent_id"));
				ol.setRoom_code(rs.getString("room_code"));
				ol.setUse_area(rs.getString("use_area"));
				ol.setWater(rs.getFloat("water"));
				return ol;
			}
			
		});
	}

	/*
	 * 水电单价改动,批量更新欠费信息
	 * @see com.yhty.dao.OweDao#batchUpdateWe(com.yhty.model.WeDefine)
	 */
	public void batchUpdateWe(final List<Owe> oList) {
		String sql = "update owe set water=?,electric=?,all=?" +
				" where id=?";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setFloat(1, oList.get(i).getOwe().getWater());
				ps.setFloat(2, oList.get(i).getOwe().getElectric());
				ps.setFloat(3, oList.get(i).getAll());
				ps.setLong(4, oList.get(i).getId());
			}
			
			public int getBatchSize() {
				return oList.size();
			}
		});
	}

	/*
	 * (non-Javadoc)
	 * @see com.yhty.dao.OweDao#getThisMonthOwe(java.lang.Long)
	 */
	public Owe getThisMonthOwe(Long rentId) {
		String sql = "select id as rent_id,rental as rent," +
		"managmentExpenses as manager from rent where id="+rentId;
		Owe owe = null;
		try{
			owe = jdbcTemplate.queryForObject(sql, new RowMapper<Owe>(){
		
			public Owe mapRow(ResultSet rs, int rowNum) throws SQLException {
				Owe o = new Owe();
				Pay owe = new Pay();
				owe.setRent(rs.getFloat("rent"));
				owe.setManager(rs.getFloat("manager"));
				o.setOwe(owe);
				o.setRent_id(rs.getLong("rent_id"));
				return o;
			}
			
		});
		}catch(EmptyResultDataAccessException e){
			return null;
		}
		return owe;
	}

	
}
